package dao.impl;

import dao.StudentDao;
import domain.Student;

import java.util.Date;

import java.sql.*;
import java.text.SimpleDateFormat;
import java.util.ArrayList;

public class StudentDaoImpl implements StudentDao {
    private static final String URL = "jdbc:mysql://localhost:8080/db1";
    private static final String USERNAME = "root";
    private static final String PASSWORD = "123456";

    @Override
    public ArrayList<Student> findAll() {
        ArrayList<Student> list = new ArrayList<>();
        Connection con = null;
        Statement statement = null;
        ResultSet rs = null;
        try {
            // 注册驱动
            Class.forName("com.mysql.cj.jdbc.Driver");
            // 获取连接
            con = DriverManager.getConnection(URL, USERNAME, PASSWORD);
            // 获取普通执行者对象
            statement = con.createStatement();
            // 执行SQL语句获取结果集
            String sql = "select * from student";
            rs = statement.executeQuery(sql);
            // 循环遍历然后绑定到对象上

            while (rs.next()) {
                Integer sid = rs.getInt("sid");
                String name = rs.getString("name");
                Integer age = rs.getInt("age");
                Date birthday = rs.getDate("birthday");
                Student student = new Student(sid, name, age, birthday);// 这个地方顺序要和上面获取的顺序一样 要不然类型对不上
                list.add(student);
            }
        } catch (ClassNotFoundException | SQLException e) {
            throw new RuntimeException(e);
        } finally {
            // 释放资源
            if (con != null) {
                try {
                    con.close();
                } catch (SQLException e) {
                    throw new RuntimeException(e);
                }
            }
            if (statement != null) {
                try {
                    statement.close();
                } catch (SQLException e) {
                    throw new RuntimeException(e);
                }
            }
            if (rs != null) {
                try {
                    rs.close();
                } catch (SQLException e) {
                    throw new RuntimeException(e);
                }
            }
        }
        // 返回集合对象
        return list;
    }

    @Override
    public Student findById(Integer id) {
        Student stu = new Student();
        Connection conn = null;
        Statement statement = null;
        ResultSet rs = null;
        try {
            // 获取注册驱动
            Class.forName("com.mysql.cj.jdbc.Driver");
            // 获取连接
            conn = DriverManager.getConnection(URL, USERNAME, PASSWORD);
            // 获取普通执行者
            statement = conn.createStatement();
            // 执行SQL语句 处理结果
            String sql = "select * from where sid ='" + id + "' ";
            statement.executeQuery(sql);
            // 处理结果

            while (rs.next()) {
                // 获取数据
                Integer sid = rs.getInt("sid");
                String name = rs.getString("name");
                Integer age = rs.getInt("age");
                Date birthday = rs.getDate("birthday");
                // 封装对象
                stu.setSid(sid);
                stu.setName(name);
                stu.setAge(age);
                stu.setBirthday(birthday);
            }
        } catch (ClassNotFoundException | SQLException e) {
            throw new RuntimeException(e);
        } finally {
            // 释放资源
            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException e) {
                    throw new RuntimeException(e);
                }
            }
            if (statement != null) {
                try {
                    statement.close();
                } catch (SQLException e) {
                    throw new RuntimeException(e);
                }
            }
            if (rs != null) {
                try {
                    rs.close();
                } catch (SQLException e) {
                    throw new RuntimeException(e);
                }
            }
            return stu;
        }
    }

    @Override
    public int insert(Student student) {
        Connection conn = null;
        Statement statement = null;
        int result = 0;
        try {
            // 注册驱动
            Class.forName("com.cj.mysql.jdbc.Driver");
            // 获取数据库连接
            conn = DriverManager.getConnection(URL, USERNAME, PASSWORD);
            // 获取普通执行者
            statement = conn.createStatement();
            // 执行sql 处理结果
            Date date = (Date) student.getBirthday();
            SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
            String brithday = sdf.format(date);
            String sql = "insert into student values( sid ='" + student.getSid() + "', name='" + student.getName() + "','" +
                    student.getAge() + "','" + brithday + "')  ";
            result = statement.executeUpdate(sql);// 执行update返回int结果
        } catch (ClassNotFoundException | SQLException e) {
            throw new RuntimeException(e);
        } finally {
            // 释放资源
            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException e) {
                    throw new RuntimeException(e);
                }
            }
            if (statement != null) {
                try {
                    statement.close();
                } catch (SQLException e) {
                    throw new RuntimeException(e);
                }
            }

        }
        return result;
    }

    @Override
    public int delete(Integer id) {
        Connection conn = null;
        Statement statement = null;
        int result = 0;
        try {
            // 注册数据库驱动
            Class.forName("com.mysql.cj.jdbc.Driver");
            // 与数据库建立连接
            conn = DriverManager.getConnection(URL, USERNAME, PASSWORD);
            // 获取普通执行者
            statement = conn.createStatement();
            // 执行sql
            String sql = "delete from student where sid ='" + id + "'";
            result = statement.executeUpdate(sql);// 巧合的是 删除这一块也是用update返回int结果
        } catch (ClassNotFoundException | SQLException e) {
            throw new RuntimeException(e);
        } finally {
            // 释放资源
            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException e) {
                    throw new RuntimeException(e);
                }
            }
            if (statement != null) {
                try {
                    statement.close();
                } catch (SQLException e) {
                    throw new RuntimeException(e);
                }
            }
        }
        return result;
    }

    @Override
    public int update(Student student) {
        Connection conn = null;
        Statement statement = null;
        int result = 0;
        try {
            // 加载和注册数据库驱动
            Class.forName("com.mysql.cj.jdbc.Driver");
            // 与数据库获取连接
            conn = DriverManager.getConnection(URL, USERNAME, PASSWORD);
            // 获取普通执行者对象
            statement = conn.createStatement();
            // 执行sql
            Date date = student.getBirthday();
            SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
            String birthday = sdf.format(date);
            String sql = "update student set sid =  '" + student.getSid() + "',name='" + student.getName() + "','" + student.getAge() + "','" + birthday +
                    "'  ";
            result = statement.executeUpdate(sql);
        } catch (ClassNotFoundException | SQLException e) {
            throw new RuntimeException(e);
        }finally {
            //释放资源
            if(conn!=null){
                try {
                    conn.close();
                } catch (SQLException e) {
                    throw new RuntimeException(e);
                }
            }
            if(statement!=null){
                try {
                    statement.close();
                } catch (SQLException e) {
                    throw new RuntimeException(e);
                }
            }
        }
        return result;
    }

}
